package com.pbdevj.jdbc.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pbdevj.jdbc.dao.EmployeeDao;
import com.pbdevj.jdbc.model.Employee;
import com.pbdevj.jdbc.util.JdbcUtil;

public class EmployeeDaoImpl implements EmployeeDao
{
	private Connection conn = null;
	
	private PreparedStatement ps = null;
	
	private ResultSet rs = null;

	@Override
	public void addEmployee(Employee employee)
	{
		try
		{
			conn = JdbcUtil.getConn();
			
			//预处理sql语句
			String sql = "insert into employee(emp_name, age, dept) values(?, ?, ?)";
			
		    ps = conn.prepareStatement(sql);
		    
		    ps.setString(1, employee.getEmpName());
		    
		    ps.setInt(2, employee.getAge());
		    
		    ps.setString(3, employee.getDept());
		    
		    int count = ps.executeUpdate();
		    
		    if(count > 0)
		    {
		    	System.out.println("添加成功！");
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally//关闭连接
		{
			JdbcUtil.closeConn(null, ps, conn);
		}
	}

	@Override
	public void updateEmployee(Employee employee)
	{
		try
		{
			conn = JdbcUtil.getConn();
			
			//预处理sql语句
			String sql = "update employee set emp_name = ?, age = ?, dept = ? where id = ?";
			
		    ps = conn.prepareStatement(sql);
		    
		    ps.setString(1, employee.getEmpName());
		    
		    ps.setInt(2, employee.getAge());
		    
		    ps.setString(3, employee.getDept());
		    
		    ps.setInt(4, employee.getId());
		    
		    int count = ps.executeUpdate();
		    
		    if(count > 0)
		    {
		    	System.out.println("更新成功！");
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally//关闭连接
		{
			JdbcUtil.closeConn(null, ps, conn);
		}
	}

	@Override
	public void deleteEmployee(Employee employee)
	{
		try
		{
			conn = JdbcUtil.getConn();
			
			//预处理sql语句
			String sql = "delete from employee where id = ?";
			
		    ps = conn.prepareStatement(sql);
		    
		    ps.setInt(1, employee.getId());
		    
		    int count = ps.executeUpdate();
		    
		    if(count > 0)
		    {
		    	System.out.println("删除成功！");
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally//关闭连接
		{
			JdbcUtil.closeConn(null, ps, conn);
		}
	}

	@Override
	public Employee getEmployeeById(Integer id)
	{
		Employee employee = null;
		 
		try
		{
			conn = JdbcUtil.getConn();
			
			//预处理sql语句
			String sql = "select e.id, e.emp_name, e.age, e.dept from employee e where e.id = ?";
			
		    ps = conn.prepareStatement(sql);
		    
		    ps.setInt(1, id);
		    
		    //把从数据库中查出的所有数据放到ResultSet中
		    rs = ps.executeQuery();
		    
		    //5.把结果集中的记录迭代出来的每一条记录放到一个Employee对象中
		    if(rs.next())
		    {
		    	employee = new Employee(rs.getInt("id"), rs.getString("emp_name"), rs.getInt("age"), rs.getString("dept"));
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally//关闭连接
		{
			JdbcUtil.closeConn(rs, ps, conn);
		}
		
		return employee;
	}

	@Override
	public List<Employee> getAllEmployees()
	{
		List<Employee> list = new ArrayList<>();
		
		try
		{
			conn = JdbcUtil.getConn();
			
			//3.预处理sql语句
			String sql = "select e.id, e.emp_name, e.age, e.dept from employee e order by e.id";
			
		    ps = conn.prepareStatement(sql);
		    
		    //4.把从数据库中查出的所有数据放到ResultSet中
		    rs = ps.executeQuery();
		    
		    Employee employee = null;
		    
		    //5.把结果集中的记录迭代出来的每一条记录放到一个Employee对象中
		    while(rs.next())
		    {
		    	Integer empId = rs.getInt("id");
		    	
		    	String empName = rs.getString("emp_name");
		    	
		    	Integer age = rs.getInt("age");
		    	
		    	String dept = rs.getString("dept");
		    	
		    	employee = new Employee(empId, empName, age, dept);
		    	
		    	list.add(employee);
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally//6.关闭连接
		{
			JdbcUtil.closeConn(rs, ps, conn);
		}
		
		
		return list;
	}

}
